package com.jain.webcore.database;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JdbcUtils {
    /**
     * The Driver.
     */
    private String driver = null;
    /**
     * The Url.
     */
    private String url = null;
    /**
     * The User.
     */
    private String user = null;
    /**
     * The Password.
     */
    private String password = null;
    /**
     * The Connection.
     */
    private Connection connection = null;
    /**
     * The Pstmt.
     */
    private PreparedStatement pstmt = null;
    /**
     * The Result set.
     */
    private ResultSet resultSet = null;
    private DatabaseType databaseType = null;

    /**
     * Instantiates a new Jdbc utils.
     */
    public JdbcUtils() {
        databaseType = DatabaseType.SQLITE;
        this.loadConfig();
    }

    /**
     * Instantiates a new Jdbc utils.
     *
     * @param type 数据库类型
     */
    public JdbcUtils(DatabaseType type) {
        if (type == null) {
            return;
        }
        databaseType = type;
        this.loadConfig();
    }

    /**
     * 加载数据库配置信息，并给相关的属性赋值
     */
    public void loadConfig() {
        InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream(
                "jdbc.properties");
        Properties properties = new Properties();
        String dbType = databaseType.toString().toLowerCase();
        try {
            properties.load(is);
            driver = properties.getProperty(dbType + ".driver");
            url = properties.getProperty(dbType + ".url");
            user = properties.getProperty(dbType + ".user");
            password = properties.getProperty(dbType + ".password");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Gets connection.
     *
     * @return 数据库连接 connection
     * @throws ClassNotFoundException the class not found exception
     */
    public Connection getConnection() {
        if (connection == null) {
            try {
                Class.forName(driver);
                connection = DriverManager.getConnection(url, user, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return connection;
    }

    /**
     * 执行更新操作
     *
     * @param sql sql语句
     * @return 执行结果 boolean
     * @throws SQLException
     */
    public boolean update(String sql) {
        return update(sql, null);
    }

    /**
     * 执行更新操作
     *
     * @param sql    sql语句
     * @param params 执行参数
     * @return 执行结果 boolean
     * @throws SQLException
     */
    public boolean update(String sql, List<?> params) {
        boolean flag = true;
        try {
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            // 填充sql语句中的占位符
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    if (param instanceof Timestamp) {
                        Timestamp timestamp = (Timestamp) params.get(i);
                        pstmt.setTimestamp(index++, timestamp);
                    } else {
                        pstmt.setObject(index++, params.get(i));
                    }
                }
            }
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            flag = false;

        }
        return flag;
    }

    /**
     * Query result set.
     *
     * @param sql the sql
     * @return result set
     * @throws SQLException the sql exception
     */
    public ResultSet query(String sql) throws SQLException {
        List<?> queryList = null;
        return query(sql, queryList);
    }

    /**
     * Query result set.
     *
     * @param sql    the sql
     * @param params the params
     * @return result set
     * @throws SQLException
     */
    public ResultSet query(String sql, Object... params) {
        List<Object> queryList = new ArrayList<Object>();
        for (int i = 0; i < params.length; i++) {
            queryList.add(i, params[i]);
        }
        return query(sql, queryList);
    }

    /**
     * 执行查询操作
     *
     * @param sql    sql语句
     * @param params 执行参数
     * @return result set
     * @throws SQLException
     */
    public ResultSet query(String sql, List<?> params) {
        int index = 1;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }

            resultSet = pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    /**
     * Query list list.
     *
     * @param sql the sql
     * @return list
     * @throws SQLException
     */
    public List<Map<String, Object>> queryList(String sql) {
        List<?> queryList = null;
        return queryList(sql, queryList);
    }

    /**
     * Query list list.
     *
     * @param sql    the sql
     * @param params the params
     * @return list
     * @throws SQLException
     */
    public List<Map<String, Object>> queryList(String sql, Object... params) {
        List<Object> queryList = new ArrayList<Object>();
        for (int i = 0; i < params.length; i++) {
            queryList.add(i, params[i]);
        }
        return queryList(sql, queryList);
    }

    /**
     * 执行查询操作
     *
     * @param sql    sql语句
     * @param params 执行参数
     * @return list
     * @throws SQLException
     */
    public List<Map<String, Object>> queryList(String sql, List<?> params) {
        resultSet = query(sql, params);

        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        ResultSetMetaData metaData;
        try {
            metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (resultSet.next()) {
                String cols_name;
                Object cols_value;

                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= cols_len; i++) {
                    cols_name = metaData.getColumnName(i);
                    cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 执行查询操作
     *
     * @param sql sql语句
     * @return json object
     */
    public String queryJsonString(String sql) {
        JSONArray jsonArray = queryJsonArray(sql);
        return jsonArray.toString();
    }

    /**
     * 执行查询操作
     *
     * @param sql sql语句
     * @return json object
     */
    public JSONArray queryJsonArray(String sql) {
        List<Object> queryList = null;
        return queryJsonArray(sql, queryList);
    }

    /**
     * 执行查询操作
     *
     * @param sql    sql语句
     * @param params 执行参数
     * @return json object
     */
    public JSONArray queryJsonArray(String sql, Object... params) {
        List<Object> queryList = new ArrayList<Object>();
        for (int i = 0; i < params.length; i++) {
            queryList.add(i, params[i]);
        }
        return queryJsonArray(sql, queryList);
    }

    /**
     * 执行查询操作
     *
     * @param sql    sql语句
     * @param params 执行参数
     * @return json object
     */
    public JSONArray queryJsonArray(String sql, List<?> params) {
        JSONArray data = new JSONArray();

        resultSet = query(sql, params);

        ResultSetMetaData metaData;
        try {
            metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();

            while (resultSet.next()) {
                String cols_name;
                Object cols_value;

                JSONObject col = new JSONObject();
                for (int i = 0; i++ < cols_len;) {
                    cols_name = metaData.getColumnName(i);
                    cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    col.put(cols_name, cols_value);
                }
                data.put(col);
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (JSONException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return data;
    }

    /**
     * 释放资源
     */
    public void releaseConnect() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * The entry point of application.
     *
     * @param args the input arguments
     */
    public static void main(String[] args) {
        JdbcUtils jdbcUtils = new JdbcUtils(DatabaseType.SQLITE);
        jdbcUtils.getConnection();

        String sql = "select * from S1001_TABLES";
        List<Map<String, Object>> queryList = jdbcUtils.queryList(sql);
        for (Map<String, Object> map : queryList) {
            for (Object key : map.keySet()) {
                System.out.print(map.get(key).toString() + '\t');
            }
            System.out.println();
        }

    }
}
